/*
	SELECT * FROM FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES(23)
*/
CREATE FUNCTION FN_LOAD_SALES_PERSONS_WITH_SUB_ORDINATES( @SALESPERSON_ID INT )
 RETURNS @FN_RESULTS TABLE ( SALESPERSON_ID INT,
				SALES_PERSON_NAME VARCHAR(50),
				LEVEL_NAME VARCHAR(50) )
AS
BEGIN
	DECLARE @TMP_SPERSON_ID INT
	DECLARE @SALESPERSON_NAME VARCHAR(50)
	DECLARE @LEVELNAME VARCHAR(50)

	DECLARE @SALES_PERSONS_TABLE TABLE( SALESPERSON_ID INT )
	INSERT INTO @SALES_PERSONS_TABLE( SALESPERSON_ID ) VALUES ( @SALESPERSON_ID )
	SELECT TOP 1 @TMP_SPERSON_ID = SALESPERSON_ID FROM @SALES_PERSONS_TABLE
	WHILE ( @TMP_SPERSON_ID IS NOT NULL AND @TMP_SPERSON_ID > 0 )
	 BEGIN
		SELECT @SALESPERSON_NAME = SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = @TMP_SPERSON_ID AND DEACTIVATED = 0
		SELECT @LEVELNAME = FCL.LEVELNAME FROM FCSPERSONREL FCSPR, FCLEVEL FCL WHERE FCSPR.LEVELORDER = FCL.LEVELORDER AND FCSPR.SPERSONID = @TMP_SPERSON_ID
		INSERT INTO @FN_RESULTS(SALESPERSON_ID, SALES_PERSON_NAME, LEVEL_NAME)
				VALUES ( @TMP_SPERSON_ID, @SALESPERSON_NAME, @LEVELNAME )
		INSERT INTO @SALES_PERSONS_TABLE( SALESPERSON_ID )
			SELECT SPERSONID FROM FCSPERSONREL FCSPR WHERE MGRID = @TMP_SPERSON_ID

		DELETE FROM @SALES_PERSONS_TABLE WHERE SALESPERSON_ID = @TMP_SPERSON_ID
		SET @TMP_SPERSON_ID = NULL
		SET @SALESPERSON_NAME = NULL
		SET @LEVELNAME = NULL
		SELECT TOP 1 @TMP_SPERSON_ID = SALESPERSON_ID FROM @SALES_PERSONS_TABLE
	 END
RETURN
END